﻿Imports System.Data.OleDb

Public Class TagsManagement
    Inherits ConnectionClass


    Public Function SelectAllTagsVisible() As OleDbDataReader
        Try

        
        myConnection.Open()
        Dim SqlString As String = "Select * From Tags Where Visible=@f1"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", True)
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        Return dr
        myConnection.Close()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function


    Public Function SelectTagByTagId(ByVal tagId As Int32) As OleDbDataReader
        Try

            myConnection.Open()
            Dim SqlString As String = "Select * From Tags Where TagId=@f1"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", tagId)
            Dim dr As OleDbDataReader = cmd.ExecuteReader()
            Return dr
            myConnection.Close()

        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function SelectTagsByQuizIdVisible(ByVal quizId As Int32) As OleDbDataReader
        Try

            myConnection.Open()
            Dim SqlString As String = "SELECT  Tags.TagName, Tags.TagId FROM  (Quiz_Tags INNER JOIN Tags ON Quiz_Tags.TagId = Tags.TagId) WHERE (Quiz_Tags.QuizId = @f1) AND (Tags.Visible = true)"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", quizId)
            Dim dr As OleDbDataReader = cmd.ExecuteReader()
            Return dr
            myConnection.Close()

        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Sub InsertTags(ByVal userId As Int32, ByVal TagName As String)
        Try


            myConnection.Open()
            Dim SqlString As String = "INSERT Into Tags (UserId,TagName) VALUES(@f1,@f2)"
            Dim newCmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            newCmd.CommandType = CommandType.Text
            newCmd.Parameters.AddWithValue("@f1", userId)
            newCmd.Parameters.AddWithValue("@f2", TagName)
            newCmd.ExecuteNonQuery()
            myConnection.Close()
        Catch ex As Exception

        End Try
    End Sub

    Public Sub InsertQuizTag(ByVal QuizId As Int32, ByVal TagId As Int32)
        Try

            myConnection.Open()
            Dim SqlString As String = "INSERT Into Quiz_Tags (QuizId,TagId) VALUES(@f1,@f2)"
            Dim newCmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            newCmd.CommandType = CommandType.Text
            newCmd.Parameters.AddWithValue("@f1", QuizId)
            newCmd.Parameters.AddWithValue("@f2", TagId)
            newCmd.ExecuteNonQuery()
            myConnection.Close()

        Catch ex As Exception

        End Try
    End Sub

    Public Sub DeleteQuizTagByQuizId(ByVal QuizId As Int32)
        Try

        
        myConnection.Open()
        Dim SqlString As String = "Delete From Quiz_Tags where QuizId =@f1"
        Dim newCmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
        newCmd.CommandType = CommandType.Text
        newCmd.Parameters.AddWithValue("@f1", QuizId)
        newCmd.ExecuteNonQuery()
        myConnection.Close()
        Catch ex As Exception

        End Try
    End Sub


    Public Function SelectTagIdByTagName(ByVal TagName As String) As Int32
        Try

            myConnection.Open()
            Dim SqlString As String = "SELECT TagId FROM   Tags WHERE  (TagName= @f1)"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", TagName)
            Dim dr As OleDbDataReader = cmd.ExecuteReader()
            dr.Read()
            If (dr.HasRows) Then
                Dim myValue As Int32 = dr.GetValue(0)
                myConnection.Close()
                Return myValue
            Else
                myConnection.Close()
                Return 0
            End If

        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Sub EditTagByTagId(ByVal TagId As Int32, ByVal TagName As String, ByVal Visible As Boolean)

        Try


            myConnection.Open()
            Dim SqlString As String = "UPDATE   Tags Set TagName =@f1,Visible=@f2 where TagId = @f3"
            Dim newCmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            newCmd.CommandType = CommandType.Text
            newCmd.Parameters.AddWithValue("@f1", TagName)
            newCmd.Parameters.AddWithValue("@f2", Visible)
            newCmd.Parameters.AddWithValue("@f2", TagId)
            newCmd.ExecuteNonQuery()
            myConnection.Close()
        Catch ex As Exception

        End Try
    End Sub

    Public Function SelectCountTagsByTagName(ByVal tagName As String, ByVal tagId As Int32) As Int32
        Try


            myConnection.Open()
            Dim Cmd As OleDbCommand = New OleDbCommand("Select Count(TagId) from Tags where TagName = @f1 and TagId <> @f2 Group By TagId ", myConnection)
            Cmd.Parameters.AddWithValue("@f1", tagName)
            Cmd.Parameters.AddWithValue("@f2", tagId)
            Dim dr = Cmd.ExecuteReader()
            If (dr.HasRows) Then
                dr.Read()
                Dim countTags = dr.GetValue(0)
                Return countTags
            Else
                Return 0
            End If

            myConnection.Close()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

End Class
